﻿
create proc [dbo].[SaveContactDetails] 
@ContactID int,
@UserID int,
@Addresses dbo.udtAddressList readonly,
@ContactOppotunities dbo.udtContactsOpportunities readonly,
@Principals dbo.udtContactsPrincipals readonly
as
begin
    set nocount on
    
    declare @ErrorMessage nvarchar(400) = 'SaveContactDetails: ',
			@SetTran bit = 0,
			@UserName nvarchar(25),
			@Now datetime2(7) = sysdatetime(),
			@AddressID int = 0,
			@AddressTypeID int,
			@IsActive bit,
			@Street nvarchar(50) = null,
			@StreetAdditional nvarchar(50) = null,
			@ZipCodeID int = null,
			@OpportunityID int = 0,
			@PrincipalID int = 0,
			@Opportunity nvarchar(50) = null, 
			@OpportunityTypeID int = 0;
    
    
    
    if  @UserID is null
    begin
        set @ErrorMessage += '@UserID is null'
        
        goto ExitOnError
    end
    
	select	@UserName = coalesce(u.LoginName, 'NA.')
	from	[User].datUser u
	where	u.UserID = @UserID

	if xact_state() = 0
	begin
		set @SetTran = 1
		begin tran
	end
    
    begin try
        
        

	--=========================			Ende   =============================================================

	--=========================			Adressen   =============================================================

	delete	m
	from	dbo.mnContactsAddress m
			left join @Addresses adr on
				adr.AddressID = m.AddressID
	where	m.ContactID = @ContactID
			and adr.AddressID is null

	declare cAdresses cursor for
	select	a.AddressID, a.AddressTypeID, a.IsActive, a.Street, a.StreetAdditional, a.ZipCodeID
	from	@Addresses a
	
	open cAdresses

	fetch next from cAdresses
	into @AddressID, @AddressTypeID, @IsActive, @Street, @StreetAdditional, @ZipCodeID

	while @@FETCH_STATUS = 0
	begin
		if not exists(select 1 from dbo.datAddress where AddressID = @AddressID)
		begin
			insert into dbo.datAddress (Street, StreetAdditional, ZipCodeID, IsActive, OrgKey)
			select @Street, @StreetAdditional, @ZipCodeID, @IsActive, @ContactID;

			select @AddressID = SCOPE_IDENTITY();
			
			print '@AddressID ' + cast(@AddressID as nvarchar(20))

			insert into dbo.mnContactsAddress (ContactID, AddressID, AddressTypeID)
			select @ContactID, @AddressID, @AddressTypeID;
		end
		else begin
			update dbo.datAddress
				set Street = @Street, 
					StreetAdditional = @StreetAdditional, 
					ZipCodeID = @ZipCodeID, 
					IsActive = @IsActive, 
					OrgKey = @ContactID
			where AddressID = @AddressID
		end

		fetch next from cAdresses
		into @AddressID, @AddressTypeID, @IsActive, @Street, @StreetAdditional, @ZipCodeID
	end

	close cAdresses;
	deallocate cAdresses;


	

	--=========================			Ende   =============================================================


	--=========================			Opportunity   =============================================================

	delete	m
	from	dbo.mnContactsOpportunity m
			left join @ContactOppotunities o on
				o.OpportunityID = m.OpportunityID
				and m.ContactID = @ContactID
	where	m.ContactID = @ContactID
			and o.OpportunityID is null;

	declare cOpportunities cursor for
	select	o.OpportunityID, o.IsActive, o.Opportunity, o.OpportunityTypeID
	from	@ContactOppotunities o
	
	open cOpportunities

	fetch next from cOpportunities
	into @OpportunityID, @IsActive, @Opportunity, @OpportunityTypeID

	while @@FETCH_STATUS = 0
	begin
		if not exists(select 1 from dbo.datOpportunity where OpportunityID = @OpportunityID)
		begin
			insert into dbo.datOpportunity (Opportunity, IsActive, OrgKey)
			select @Opportunity, @IsActive, @ContactID;

			select @OpportunityID = SCOPE_IDENTITY();

			print '@OpportunityID ' + cast(@OpportunityID as nvarchar(20))

			insert into dbo.mnContactsOpportunity(ContactID, OpportunityID, OpportunityTypeID)
			select @ContactID, @OpportunityID, @OpportunityTypeID;
		end
		else begin
			update dbo.datOpportunity
				set Opportunity = @Opportunity, 
					IsActive = @IsActive, 
					OrgKey = @ContactID
			where OpportunityID = @OpportunityID
		end

		fetch next from cOpportunities
		into @OpportunityID, @IsActive, @Opportunity, @OpportunityTypeID
	end

	close cOpportunities;
	deallocate cOpportunities;

	


	--=========================			Ende   =============================================================

	

	--=========================			Principals   =============================================================
	 
	with principals
	as
	(
		select	p.ContactID,
				p.PrincipalID,
				p.FunctionKey,
				p.JobtitleKey,
				p.PhoneExtension,
				p.Fax,
				p.Job
		from	@Principals p
	)
	merge into [dbo].[mnContactsPrincipals] t
		using principals s on
			s.ContactID = t.ContactID
			and s.PrincipalID = t.PrincipalID
	when not matched by target then
		insert (
			ContactID, 
			PrincipalID, 
			FunctionKey, 
			JobtitleKey, 
			PhoneExtension, 
			Fax, 
			Job
		)
		values (
			s.ContactID, 
			s.PrincipalID, 
			s.FunctionKey, 
			s.JobtitleKey, 
			s.PhoneExtension, 
			s.Fax, 
			s.Job
		)
		when matched then
		update
			set	
				FunctionKey = s.FunctionKey,
				JobtitleKey = s.JobtitleKey, 
				PhoneExtension = s.PhoneExtension, 
				Fax = s.Fax, 
				Job = s.Job
			;


	--=========================			Ende   =============================================================


	


    end try
    begin catch
        print ''
		goto ExitOnError
    end catch
    
	if @SetTran = 1
	begin
		commit tran
	end
    
    select  @ContactID 'ContactID';
    
    return 0;
    
    ExitOnError:
        if  @SetTran = 0
        begin
            rollback tran
        end
        
        raiserror(@ErrorMessage, 16, 2)
        
        return 1
end